{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Declare the required imports\r\n",
    "import json\r\n",
    "import time\r\n",
    "import pandas as pd\r\n",
    "import numpy as np\r\n",
    "\r\n",
    "import matplotlib.pyplot as plt\r\n",
    "import urllib.request\r\n",
    "import urllib.parse\r\n",
    "import datetime\r\n",
    "\r\n",
    "# Define the variables to access the Microsoft Threat protection API using Azure APP\r\n",
    "# Replace with your Tenant ID\r\n",
    "tenantId = '' \r\n",
    "# Replace with your Application ID\r\n",
    "appId = '' \r\n",
    "# Replace with the Secret for your Application\r\n",
    "appSecret = '' \r\n",
    "# Replace with the input JSON file with the queries you want to run\r\n",
    "query_inputfile_fullpath = \"C:\\\\temp\\\\singleQUery.json\" \r\n",
    "#Define the folder to save the query results as CSV\r\n",
    "query_outputfolder_fullpath = \"C:\\\\Temp\\\\output\" \r\n",
    "# Define the Teams channel\r\n",
    "teamsurl = \"https://microsoft.webhook.office.com/webhookb2/.....\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def app_auth():\n",
    "    url = \"https://login.windows.net/%s/oauth2/token\" % (tenantId) # Login OAUTH2 page\n",
    "    resourceAppIdUri = 'https://api.security.microsoft.com' # M365 Api\n",
    "\n",
    "    body = {\n",
    "        'resource' : resourceAppIdUri,\n",
    "        'client_id' : appId,\n",
    "        'client_secret' : appSecret,\n",
    "        'grant_type' : 'client_credentials'\n",
    "    }\n",
    "\n",
    "    data = urllib.parse.urlencode(body).encode(\"utf-8\")\n",
    "\n",
    "    req = urllib.request.Request(url, data)\n",
    "    response = urllib.request.urlopen(req)\n",
    "    jsonResponse = json.loads(response.read())\n",
    "    aadToken = jsonResponse[\"access_token\"] # Access token for the next hour\n",
    "\n",
    "    return aadToken\n",
    "\n",
    "# Declare a function to query the M365 Defender API\n",
    "def exec_mtp_query(query):\n",
    "    url = \"https://api.security.microsoft.com/api/advancedhunting/run\" #M365 Advanced Hunting API\n",
    "    headers = { \n",
    "    'Content-Type' : 'application/json',\n",
    "    'Accept' : 'application/json',\n",
    "    'Authorization' : \"Bearer \" + aadToken\n",
    "    }\n",
    "\n",
    "    data = json.dumps({ 'Query' : query }).encode(\"utf-8\")\n",
    "\n",
    "    req = urllib.request.Request(url, data, headers)\n",
    "    response = urllib.request.urlopen(req)\n",
    "    jsonResponse = json.loads(response.read())\n",
    "    schema = jsonResponse[\"Schema\"]\n",
    "    results = jsonResponse[\"Results\"]\n",
    "    \n",
    "    df = pd.DataFrame(results)\n",
    "    \n",
    "    return df\n",
    "\n",
    "# Declaring a function to calculate the elapsed time during the query\n",
    "def getTimeDifferenceFromNow(TimeStart, TimeEnd):\n",
    "    timeDiff = TimeEnd - TimeStart\n",
    "    return timeDiff.total_seconds() / 60\n",
    "\n",
    "# This function will send data to teams\n",
    "def send_message_to_teams(messagetosend):\n",
    "    import requests\n",
    "    payload = {\n",
    "        \"text\": messagetosend\n",
    "    }\n",
    "    headers = {\n",
    "        'Content-Type': 'application/json'\n",
    "    }\n",
    "    response = requests.post(teamsurl, headers=headers, data=json.dumps(payload))\n",
    "    if response.text == \"1\":\n",
    "        print(\"Message Successfully posted in teams\")\n",
    "    else:\n",
    "        print(\"Message post in teams failed\")\n",
    "    return response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "TimeStart = datetime.datetime.now()\n",
    "TimeEnd = datetime.datetime.now()\n",
    "aadToken = app_auth()\n",
    "\n",
    "with open(query_inputfile_fullpath) as json_file:\n",
    "    data = json.load(json_file)\n",
    "    i = 1\n",
    "    for p in data:\n",
    "        ###\n",
    "        str_per = (i/len(data))*100\n",
    "        \n",
    "        # check if we need a new token\n",
    "        Elapsed_Minutes = getTimeDifferenceFromNow(TimeStart, TimeEnd)\n",
    "        if round(Elapsed_Minutes) < 45:\n",
    "            print('Using existing AzAD token')\n",
    "        else:\n",
    "            print('Generate a new AzAD token')\n",
    "            aadToken = app_auth()\n",
    "            TimeStart = datetime.datetime.now()\n",
    "\n",
    "\n",
    "        print('\\x1b[0;30;44m' + \"Running\",i,\"of\",len(data),\" queries, we are at the \",'%.2f' %str_per,\"% of processing rules\"+ '\\x1b[0m')\n",
    "\n",
    "        # Set the Query name from JSON source file\n",
    "        query_name = p['Name']\n",
    "        # Making all the columns visible\n",
    "        pd.set_option('display.max_columns', None)\n",
    "\n",
    "        # Expanding the output of the display to be more visible\n",
    "        pd.set_option('max_colwidth', 200)\n",
    "\n",
    "        print('\\x1b[6;30;42m' + \"Running the\",query_name, \"query\" + '\\x1b[0m')\n",
    "\n",
    "        # Define the query from JSON source file\n",
    "        kql_query = p['Query']\n",
    "        if len(kql_query) != 0:\n",
    "            print('\\x1b[0;30;47m' + kql_query + '\\x1b[0m')\n",
    "            try:\n",
    "                query_results = exec_mtp_query(kql_query)\n",
    "            except Exception as query_error:\n",
    "                print(query_error)\n",
    "            else:\n",
    "                if len(query_results) == 0:\n",
    "                    # wait 4 second to don't exeeed the 15 queries per 60 seconds limit\n",
    "                    time.sleep(4)\n",
    "                    print('\\x1b[6;30;42m' + \"No results for the\",query_name, \"query\" + '\\x1b[0m')\n",
    "                else:\n",
    "                    if len(query_results) == 10000:\n",
    "                        print('\\x1b[6;30;42m' + \"The results for the\",query_name, \"query are exeeding the 10,000 limits\" + '\\x1b[0m')\n",
    "                    print('\\x1b[6;30;42m' + \"Results for the\",query_name, \"query\" + '\\x1b[0m')\n",
    "                    print('\\x1b[6;30;42m' + \"The query returns\",len(query_results), \"results\" + '\\x1b[0m')\n",
    "                    messagetosend = 'The query: ' +query_name + ', returns ' + str(len(query_results)) + ' results'\n",
    "                    \n",
    "                    if i == 1:\n",
    "                        QueryResultsSummary = pd.DataFrame([[query_name,len(query_results)]], columns = ['QueryName','QueryResults'])\n",
    "                    else:\n",
    "                        QueryResultsSummary_temp = pd.DataFrame([[query_name,len(query_results)]], columns = ['QueryName','QueryResults'])\n",
    "                        QueryResultsSummary = QueryResultsSummary.append(QueryResultsSummary_temp)\n",
    "\n",
    "                    display(query_results.head())\n",
    "                    # save to Tsv\n",
    "                    try:\n",
    "                        file_out = query_outputfolder_fullpath+'\\\\'+query_name+'.tsv'\n",
    "                    except Exception as file_export_error:\n",
    "                        print(file_export_error)\n",
    "                    else: \n",
    "                        query_results.to_csv(file_out, sep=\"\\t\")\n",
    "                i = i +1\n",
    "        TimeEnd = datetime.datetime.now()\n",
    "\n",
    "# Send message summary to Teams\n",
    "messagetosend = QueryResultsSummary.to_html(index = False)\n",
    "#send_message_to_teams(messagetosend)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.5 64-bit (conda)",
   "name": "python38564bitconda621362638e45467d889ff2fbfac97eae"
  },
  "language_info": {
   "name": "python",
   "version": ""
  },
  "metadata": {
   "interpreter": {
    "hash": "ecf5722fdaf1897a315d257d89d94520bfcaa453217d5becf09b39e73618b0de"
   }
  },
  "orig_nbformat": 2
 },
 "nbformat": 4,
 "nbformat_minor": 2
}